﻿--
-- PostgreSQL database dump
--

-- Started on 2011-06-08 20:38:08 BRT

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 379 (class 2612 OID 17618)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;

ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- TOC entry 322 (class 1247 OID 17614)
-- Dependencies: 3
-- Name: dm_cpf; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_cpf AS character(14) NOT NULL;


ALTER DOMAIN public.dm_cpf OWNER TO postgres;

--
-- TOC entry 320 (class 1247 OID 17612)
-- Dependencies: 3
-- Name: dm_local; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_local AS character varying(80);


ALTER DOMAIN public.dm_local OWNER TO postgres;

--
-- TOC entry 317 (class 1247 OID 17609)
-- Dependencies: 3
-- Name: dm_nome; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_nome AS character varying(100) NOT NULL;


ALTER DOMAIN public.dm_nome OWNER TO postgres;

--
-- TOC entry 318 (class 1247 OID 17610)
-- Dependencies: 3
-- Name: dm_nome2; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_nome2 AS character varying(100);


ALTER DOMAIN public.dm_nome2 OWNER TO postgres;

--
-- TOC entry 323 (class 1247 OID 17615)
-- Dependencies: 3
-- Name: dm_rg; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_rg AS character(12) NOT NULL;


ALTER DOMAIN public.dm_rg OWNER TO postgres;

--
-- TOC entry 319 (class 1247 OID 17611)
-- Dependencies: 3
-- Name: dm_telefone; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_telefone AS character varying(14);


ALTER DOMAIN public.dm_telefone OWNER TO postgres;

--
-- TOC entry 321 (class 1247 OID 17613)
-- Dependencies: 3
-- Name: dm_uf; Type: DOMAIN; Schema: public; Owner: postgres
--

CREATE DOMAIN dm_uf AS character(2) NOT NULL;

ALTER DOMAIN public.dm_uf OWNER TO postgres;


--
-- TOC entry 375 (class 1247 OID 18038)
-- Dependencies: 3 1592
-- Name: tp_profissional_profissao; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE tp_profissional_profissao AS (
	codprofissional integer,
	nome dm_nome,
	codprofissao integer,
	profissao dm_nome,
	nivel smallint
);


ALTER TYPE public.tp_profissional_profissao OWNER TO postgres;

--
-- TOC entry 377 (class 1247 OID 18041)
-- Dependencies: 3 1593
-- Name: tp_solicitacoes; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE tp_solicitacoes AS (
	codcliente bigint,
	cliente dm_nome,
	quantidade integer,
	codprofissao integer,
	profissao dm_nome,
	datasolicitacao date,
	dataatendimento date
);


ALTER TYPE public.tp_solicitacoes OWNER TO postgres;

--
-- TOC entry 23 (class 1255 OID 18051)
-- Dependencies: 3
-- Name: fn_atualizanivel(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_atualizanivel(integer, integer, integer) RETURNS smallint
    LANGUAGE sql
    AS $_$
	-- atualizando
	UPDATE Especializacao set nivel = nivel + $3 
		WHERE ProfissionalCodProfissional = $1 AND profissaoCodProfissao = $2;
	
	-- retornando nivel
	select nivel from vw_profissional_profissao
		where codprofissional = 1 and codprofissao = 1
		limit 1
$_$;


ALTER FUNCTION public.fn_atualizanivel(integer, integer, integer) OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1567 (class 1259 OID 17864)
-- Dependencies: 317 3
-- Name: area; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--
drop table users;
create table users (
	id	serial primary key,
	nome	dm_nome,
	tipo	char(1) check (tipo = 'C' or tipo = 'P' or tipo = 'O'),
	codigo	integer,
	email	dm_local,
	ativo	boolean default true,
	login	varchar(10),
	senha	varchar(15) default ''
)

ALTER TABLE public.users OWNER TO postgres;

alter table public.users add constraint login_unq unique (login);

insert into users (nome, tipo, login) values ('Administrador','O','admin');

select * from users;

-- -------------------------------------------------------------------------

CREATE TABLE area (
    codarea integer NOT NULL,
    descricao dm_nome
);


ALTER TABLE public.area OWNER TO postgres;

--
-- TOC entry 33 (class 1255 OID 18581)
-- Dependencies: 325 3
-- Name: fn_filtrar_area(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtrar_area(text) RETURNS SETOF area
    LANGUAGE sql
    AS $_$
	SELECT * FROM Area WHERE LOWER(descricao) LIKE fn_NoCase($1)
$_$;


ALTER FUNCTION public.fn_filtrar_area(text) OWNER TO postgres;

--
-- TOC entry 1572 (class 1259 OID 17897)
-- Dependencies: 3
-- Name: especializacao; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE especializacao (
    profissionalcodprofissional integer NOT NULL,
    profissaocodprofissao integer NOT NULL,
    dataespecializacao date NOT NULL,
    nivel smallint NOT NULL
);


ALTER TABLE public.especializacao OWNER TO postgres;

--
-- TOC entry 1569 (class 1259 OID 17875)
-- Dependencies: 3 317
-- Name: profissao; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE profissao (
    codprofissao integer NOT NULL,
    descricao dm_nome NOT NULL,
    areacodarea integer NOT NULL
);


ALTER TABLE public.profissao OWNER TO postgres;

--
-- TOC entry 1571 (class 1259 OID 17886)
-- Dependencies: 319 319 318 321 323 3 320 322 317 320 320
-- Name: profissional; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE profissional (
    codprofissional integer NOT NULL,
    cpf dm_cpf,
    nome dm_nome,
    fantasia dm_nome2,
    sexo character(1) NOT NULL,
    fonefixo dm_telefone,
    fonecel dm_telefone,
    endereco dm_local,
    bairro dm_local,
    cidade dm_local,
    uf dm_uf,
    rg_num dm_rg,
    rg_org_exp character(6) NOT NULL,
    rg_data_exp date NOT NULL,
    carteiraprofissional character varying(14) NOT NULL,
    cnh_registro character varying(12),
    cnh_cat character varying(10),
    cnh_vencto date,
    datanascimento date NOT NULL,
    alocado boolean NOT NULL
);


ALTER TABLE public.profissional OWNER TO postgres;

--
-- TOC entry 1579 (class 1259 OID 17968)
-- Dependencies: 1681 317 3 317
-- Name: vw_profissional_profissao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_profissional_profissao AS
    SELECT profissional.codprofissional, profissional.nome, profissao.codprofissao, profissao.descricao AS profissao, especializacao.nivel FROM ((especializacao JOIN profissional ON ((profissional.codprofissional = especializacao.profissionalcodprofissional))) JOIN profissao ON ((profissao.codprofissao = especializacao.profissaocodprofissao)));


ALTER TABLE public.vw_profissional_profissao OWNER TO postgres;

--
-- TOC entry 1587 (class 1259 OID 18003)
-- Dependencies: 1689 317 3 317
-- Name: vwr_profissionais_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_profissionais_solicitacao AS
    SELECT vw_profissional_profissao.profissao, vw_profissional_profissao.nome, vw_profissional_profissao.nivel FROM vw_profissional_profissao ORDER BY vw_profissional_profissao.codprofissao, vw_profissional_profissao.nivel DESC, vw_profissional_profissao.nome;


ALTER TABLE public.vwr_profissionais_solicitacao OWNER TO postgres;

--
-- TOC entry 32 (class 1255 OID 18538)
-- Dependencies: 365 3
-- Name: fn_filtrar_area_quantidade(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtrar_area_quantidade(text, text) RETURNS SETOF vwr_profissionais_solicitacao
    LANGUAGE sql
    AS $_$
	select * from vwr_profissionais_solicitacao
		where lower(profissao) like fn_NoCase($1)
		and lower(nome) like fn_NoCase($2)

$_$;


ALTER FUNCTION public.fn_filtrar_area_quantidade(text, text) OWNER TO postgres;

--
-- TOC entry 29 (class 1255 OID 18582)
-- Dependencies: 329 3
-- Name: fn_filtrar_profissao(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_filtrar_profissao(integer) RETURNS SETOF profissao
    LANGUAGE sql
    AS $_$
	select * from Profissao where AreaCodArea = $1
$_$;


ALTER FUNCTION public.fn_filtrar_profissao(integer) OWNER TO postgres;

--
-- TOC entry 28 (class 1255 OID 18056)
-- Dependencies: 3
-- Name: fn_get_area_profissao(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_get_area_profissao(text) RETURNS integer
    LANGUAGE sql
    AS $_$
	select AreaCodArea from Profissao where Profissao.Descricao = $1 limit 1;
$_$;


ALTER FUNCTION public.fn_get_area_profissao(text) OWNER TO postgres;

--
-- TOC entry 19 (class 1255 OID 18047)
-- Dependencies: 3
-- Name: fn_getclienteid(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_getclienteid(text) RETURNS bigint
    LANGUAGE sql
    AS $_$
	select CodCliente from Cliente where nomecli = $1;
$_$;


ALTER FUNCTION public.fn_getclienteid(text) OWNER TO postgres;

--
-- TOC entry 20 (class 1255 OID 18048)
-- Dependencies: 3
-- Name: fn_getprofissionalid(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_getprofissionalid(text) RETURNS integer
    LANGUAGE sql
    AS $_$
	select CodProfissional from Profissional where nome = $1;
$_$;


ALTER FUNCTION public.fn_getprofissionalid(text) OWNER TO postgres;

--
-- TOC entry 30 (class 1255 OID 18057)
-- Dependencies: 3 379
-- Name: fn_inserir_especializacao(text, text, date, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_inserir_especializacao(novo_profissional text, nova_profissao text, data_especializacao date, novo_nivel integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	cod_profissional integer;
	cod_profissao integer;
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- verifica se tem a area da profissao
	select into cod_profissao CodProfissao from Profissao where Profissao.Descricao = nova_profissao;
	
	if FOUND then
		-- verifica se tem o profissional
		cod_profissional := fn_getProfissionalId(novo_profissional);
		raise notice 'quanto deu o found %, %', FOUND, cod_profissional;
		raise notice 'FOUND and % = %', (cod_profissional), (FOUND and cod_profissional>0);
		if (FOUND and cod_profissional>0) then
			raise notice 'INSERT INTO Especializacao (ProfissionalCodProfissional, ProfissaoCodProfissao, DataEspecializacao, Nivel) VALUES (%,%,%,%);', cod_profissional, cod_profissao, data_especializacao, novo_nivel;
			INSERT INTO Especializacao
				(ProfissionalCodProfissional, ProfissaoCodProfissao, DataEspecializacao, Nivel) 
				VALUES (cod_profissional, cod_profissao, data_especializacao, novo_nivel);	
			GET DIAGNOSTICS var_sistema := ROW_COUNT;
			return var_sistema > 0;
		end if;
	end if;
	return false;
END;
$$;


ALTER FUNCTION public.fn_inserir_especializacao(novo_profissional text, nova_profissao text, data_especializacao date, novo_nivel integer) OWNER TO postgres;

--
-- TOC entry 26 (class 1255 OID 18054)
-- Dependencies: 379 3
-- Name: fn_inserir_profissao(text, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_inserir_profissao(nova_area text, nova_profissao text) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	cod_area integer;
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- verificar se existe area
	select into cod_area * from area where area.Descricao = nova_area;

	if FOUND then
		-- inserir profissao na area
		INSERT INTO Profissao(Descricao, AreaCodArea) VALUES (nova_profissao, cod_area);
	else
		-- criar area e inserir (recursivo)
		INSERT INTO Area(Descricao) VALUES (nova_area);
		return fn_inserir_profissao(nova_area, nova_profissao);
	end if;
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	
	return var_sistema > 0;
END;
$$;


ALTER FUNCTION public.fn_inserir_profissao(nova_area text, nova_profissao text) OWNER TO postgres;

--
-- TOC entry 21 (class 1255 OID 18049)
-- Dependencies: 3 375
-- Name: fn_lista_especializacao(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_especializacao(text) RETURNS SETOF tp_profissional_profissao
    LANGUAGE sql
    AS $_$
	select * from vw_profissional_profissao
		where nome = $1;
$_$;


ALTER FUNCTION public.fn_lista_especializacao(text) OWNER TO postgres;

--
-- TOC entry 22 (class 1255 OID 18050)
-- Dependencies: 3 375
-- Name: fn_lista_especializacao_avancado(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_especializacao_avancado(text) RETURNS SETOF tp_profissional_profissao
    LANGUAGE sql
    AS $_$
	select * from vw_profissional_profissao
		where lower(nome) like lower('%' || $1 || '%');
$_$;


ALTER FUNCTION public.fn_lista_especializacao_avancado(text) OWNER TO postgres;

--
-- TOC entry 24 (class 1255 OID 18052)
-- Dependencies: 3 377
-- Name: fn_lista_solicitacoes(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_solicitacoes(text) RETURNS SETOF tp_solicitacoes
    LANGUAGE sql
    AS $_$
	select * from vw_solicitacoes 
		where lower(cliente) like lower('%' || $1 || '%')
$_$;


ALTER FUNCTION public.fn_lista_solicitacoes(text) OWNER TO postgres;

--
-- TOC entry 25 (class 1255 OID 18053)
-- Dependencies: 377 379 3
-- Name: fn_lista_solicitacoes(boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_lista_solicitacoes(atendida boolean) RETURNS SETOF tp_solicitacoes
    LANGUAGE plpgsql
    AS $$
DECLARE
BEGIN
	
	if atendida then
		return query
			select * from vw_solicitacoes 
				where DataAtendimento > DataSolicitacao;
	else
		return  query
			select * from vw_solicitacoes 
				where DataAtendimento < DataSolicitacao;
	end if;
	
END;
$$;


ALTER FUNCTION public.fn_lista_solicitacoes(atendida boolean) OWNER TO postgres;

--
-- TOC entry 31 (class 1255 OID 18537)
-- Dependencies: 3
-- Name: fn_nocase(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_nocase(text) RETURNS text
    LANGUAGE sql
    AS $_$
	select lower('%' || $1 || '%');
$_$;


ALTER FUNCTION public.fn_nocase(text) OWNER TO postgres;

--
-- TOC entry 27 (class 1255 OID 18055)
-- Dependencies: 3
-- Name: fn_quantidade_solicitacoes(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION fn_quantidade_solicitacoes(text) RETURNS TABLE(codcliente bigint, cliente character varying, quantidade bigint)
    LANGUAGE sql
    AS $_$
	select codcliente, cliente, sum(quantidade) from vw_solicitacoes 
		--where lower(cliente) like lower('%' || $1 || '%')
		where cliente = $1
		group by codcliente,cliente
$_$;


ALTER FUNCTION public.fn_quantidade_solicitacoes(text) OWNER TO postgres;

--
-- TOC entry 1566 (class 1259 OID 17862)
-- Dependencies: 3 1567
-- Name: area_codarea_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE area_codarea_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.area_codarea_seq OWNER TO postgres;

--
-- TOC entry 1928 (class 0 OID 0)
-- Dependencies: 1566
-- Name: area_codarea_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE area_codarea_seq OWNED BY area.codarea;


--
-- TOC entry 1929 (class 0 OID 0)
-- Dependencies: 1566
-- Name: area_codarea_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('area_codarea_seq', 7, true);


--
-- TOC entry 1574 (class 1259 OID 17904)
-- Dependencies: 319 320 3 320 320 320 320 319 319 321 317 318
-- Name: cliente; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cliente (
    codcliente bigint NOT NULL,
    nomecli dm_nome,
    fantasia dm_nome2,
    endereco dm_local,
    bairro dm_local,
    cidade dm_local,
    uf dm_uf,
    fonefixo dm_telefone,
    fonefax dm_telefone,
    fonecelular dm_telefone,
    email dm_local,
    homepage dm_local,
    tipo character(1) NOT NULL,
    datanasctoabertura date,
    cpfcnpj character varying(18),
    rgie character varying(18)
);


ALTER TABLE public.cliente OWNER TO postgres;

--
-- TOC entry 1573 (class 1259 OID 17902)
-- Dependencies: 1574 3
-- Name: cliente_codcliente_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE cliente_codcliente_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.cliente_codcliente_seq OWNER TO postgres;

--
-- TOC entry 1930 (class 0 OID 0)
-- Dependencies: 1573
-- Name: cliente_codcliente_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE cliente_codcliente_seq OWNED BY cliente.codcliente;


--
-- TOC entry 1931 (class 0 OID 0)
-- Dependencies: 1573
-- Name: cliente_codcliente_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('cliente_codcliente_seq', 2, true);


--
-- TOC entry 1577 (class 1259 OID 17923)
-- Dependencies: 1890 3
-- Name: clientesprofissional; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE clientesprofissional (
    solicitacaocodsolicitacao bigint NOT NULL,
    profissionalcodprofissional integer DEFAULT 3 NOT NULL,
    aceito boolean,
    nivel smallint NOT NULL
);


ALTER TABLE public.clientesprofissional OWNER TO postgres;

--
-- TOC entry 1568 (class 1259 OID 17873)
-- Dependencies: 3 1569
-- Name: profissao_codprofissao_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE profissao_codprofissao_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.profissao_codprofissao_seq OWNER TO postgres;

--
-- TOC entry 1932 (class 0 OID 0)
-- Dependencies: 1568
-- Name: profissao_codprofissao_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE profissao_codprofissao_seq OWNED BY profissao.codprofissao;


--
-- TOC entry 1933 (class 0 OID 0)
-- Dependencies: 1568
-- Name: profissao_codprofissao_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('profissao_codprofissao_seq', 15, true);


--
-- TOC entry 1570 (class 1259 OID 17884)
-- Dependencies: 3 1571
-- Name: profissional_codprofissional_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE profissional_codprofissional_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.profissional_codprofissional_seq OWNER TO postgres;

--
-- TOC entry 1934 (class 0 OID 0)
-- Dependencies: 1570
-- Name: profissional_codprofissional_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE profissional_codprofissional_seq OWNED BY profissional.codprofissional;


--
-- TOC entry 1935 (class 0 OID 0)
-- Dependencies: 1570
-- Name: profissional_codprofissional_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('profissional_codprofissional_seq', 14, true);


--
-- TOC entry 1576 (class 1259 OID 17917)
-- Dependencies: 3
-- Name: solicitacao; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE solicitacao (
    codsolicitacao bigint NOT NULL,
    clientecodcliente bigint NOT NULL,
    profissaocodprofissao integer NOT NULL,
    qtde integer,
    datasolicitacao date,
    dataatendimento date
);


ALTER TABLE public.solicitacao OWNER TO postgres;

--
-- TOC entry 1575 (class 1259 OID 17915)
-- Dependencies: 1576 3
-- Name: solicitacao_codsolicitacao_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE solicitacao_codsolicitacao_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.solicitacao_codsolicitacao_seq OWNER TO postgres;

--
-- TOC entry 1936 (class 0 OID 0)
-- Dependencies: 1575
-- Name: solicitacao_codsolicitacao_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE solicitacao_codsolicitacao_seq OWNED BY solicitacao.codsolicitacao;


--
-- TOC entry 1937 (class 0 OID 0)
-- Dependencies: 1575
-- Name: solicitacao_codsolicitacao_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('solicitacao_codsolicitacao_seq', 6, true);


--
-- TOC entry 1578 (class 1259 OID 17964)
-- Dependencies: 1680 317 317 3
-- Name: vw_area_profissao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_area_profissao AS
    SELECT a.codarea, a.descricao AS area, p.codprofissao, p.descricao AS profissao FROM (area a JOIN profissao p ON ((a.codarea = p.areacodarea)));


ALTER TABLE public.vw_area_profissao OWNER TO postgres;

--
-- TOC entry 1581 (class 1259 OID 17977)
-- Dependencies: 1683 317 3 317
-- Name: vw_cliente_profissional; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_cliente_profissional AS
    SELECT s.codsolicitacao, c.codcliente, c.nomecli AS nomecliente, p.codprofissional, p.nome AS nomeprofissional, cp.nivel, cp.aceito FROM (((clientesprofissional cp JOIN profissional p ON ((p.codprofissional = cp.profissionalcodprofissional))) JOIN solicitacao s ON ((s.codsolicitacao = cp.solicitacaocodsolicitacao))) JOIN cliente c ON ((c.codcliente = s.clientecodcliente)));


ALTER TABLE public.vw_cliente_profissional OWNER TO postgres;

--
-- TOC entry 1582 (class 1259 OID 17982)
-- Dependencies: 1684 317 317 317 3 317
-- Name: vw_cliente_profissional_geral; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_cliente_profissional_geral AS
    SELECT s.codsolicitacao, c.codcliente, c.nomecli AS nomecliente, p.codprofissional, p.nome AS nomeprofissional, prof.codprofissao, prof.descricao AS profissao, a.codarea, a.descricao AS area, cp.nivel, cp.aceito FROM (((((clientesprofissional cp JOIN profissional p ON ((p.codprofissional = cp.profissionalcodprofissional))) JOIN solicitacao s ON ((s.codsolicitacao = cp.solicitacaocodsolicitacao))) JOIN cliente c ON ((c.codcliente = s.clientecodcliente))) JOIN profissao prof ON ((prof.codprofissao = s.profissaocodprofissao))) JOIN area a ON ((a.codarea = prof.areacodarea)));


ALTER TABLE public.vw_cliente_profissional_geral OWNER TO postgres;

--
-- TOC entry 1580 (class 1259 OID 17973)
-- Dependencies: 1682 317 3 317
-- Name: vw_solicitacoes; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_solicitacoes AS
    SELECT cliente.codcliente, cliente.nomecli AS cliente, solicitacao.qtde AS quantidade, profissao.codprofissao, profissao.descricao AS profissao, solicitacao.datasolicitacao, solicitacao.dataatendimento FROM ((solicitacao JOIN profissao ON ((profissao.codprofissao = solicitacao.profissaocodprofissao))) JOIN cliente ON ((cliente.codcliente = solicitacao.clientecodcliente)));


ALTER TABLE public.vw_solicitacoes OWNER TO postgres;

--
-- TOC entry 1583 (class 1259 OID 17987)
-- Dependencies: 1685 3 317
-- Name: vw_qtd_profissao_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_qtd_profissao_solicitacao AS
    SELECT vw_solicitacoes.codprofissao, vw_solicitacoes.profissao, count(vw_solicitacoes.codprofissao) AS quantia FROM vw_solicitacoes GROUP BY vw_solicitacoes.codprofissao, vw_solicitacoes.profissao;


ALTER TABLE public.vw_qtd_profissao_solicitacao OWNER TO postgres;

--
-- TOC entry 1585 (class 1259 OID 17995)
-- Dependencies: 1687 3 317
-- Name: vw_qtd_profissional_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_qtd_profissional_solicitacao AS
    SELECT vw_cliente_profissional.codprofissional, vw_cliente_profissional.nomeprofissional, count(vw_cliente_profissional.codprofissional) AS quantia FROM vw_cliente_profissional GROUP BY vw_cliente_profissional.codprofissional, vw_cliente_profissional.nomeprofissional;


ALTER TABLE public.vw_qtd_profissional_solicitacao OWNER TO postgres;

--
-- TOC entry 1584 (class 1259 OID 17991)
-- Dependencies: 1686 317 3
-- Name: vw_soma_profissao_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vw_soma_profissao_solicitacao AS
    SELECT vw_solicitacoes.codprofissao, vw_solicitacoes.profissao, sum(vw_solicitacoes.codprofissao) AS quantia FROM vw_solicitacoes GROUP BY vw_solicitacoes.codprofissao, vw_solicitacoes.profissao;


ALTER TABLE public.vw_soma_profissao_solicitacao OWNER TO postgres;

--
-- TOC entry 1586 (class 1259 OID 17999)
-- Dependencies: 1688 3 317
-- Name: vwr_area_quantidade; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_area_quantidade AS
    SELECT vw_area_profissao.area, count(vw_area_profissao.profissao) AS quantidade FROM vw_area_profissao GROUP BY vw_area_profissao.area ORDER BY count(vw_area_profissao.profissao) DESC, vw_area_profissao.area;


ALTER TABLE public.vwr_area_quantidade OWNER TO postgres;

--
-- TOC entry 1590 (class 1259 OID 18015)
-- Dependencies: 1692 317 3
-- Name: vwr_qtd_area_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_qtd_area_solicitacao AS
    SELECT vw_cliente_profissional_geral.area, count(vw_cliente_profissional_geral.codarea) AS quantia FROM vw_cliente_profissional_geral GROUP BY vw_cliente_profissional_geral.area ORDER BY count(vw_cliente_profissional_geral.codarea) DESC, vw_cliente_profissional_geral.area;


ALTER TABLE public.vwr_qtd_area_solicitacao OWNER TO postgres;

--
-- TOC entry 1588 (class 1259 OID 18007)
-- Dependencies: 1690 3 317
-- Name: vwr_qtd_profissao_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_qtd_profissao_solicitacao AS
    SELECT vw_qtd_profissao_solicitacao.profissao, vw_qtd_profissao_solicitacao.quantia FROM vw_qtd_profissao_solicitacao ORDER BY vw_qtd_profissao_solicitacao.quantia DESC, vw_qtd_profissao_solicitacao.profissao;


ALTER TABLE public.vwr_qtd_profissao_solicitacao OWNER TO postgres;

--
-- TOC entry 1589 (class 1259 OID 18011)
-- Dependencies: 1691 317 3
-- Name: vwr_qtd_profissional_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_qtd_profissional_solicitacao AS
    SELECT vw_qtd_profissional_solicitacao.nomeprofissional, vw_qtd_profissional_solicitacao.quantia FROM vw_qtd_profissional_solicitacao ORDER BY vw_qtd_profissional_solicitacao.quantia DESC, vw_qtd_profissional_solicitacao.nomeprofissional;


ALTER TABLE public.vwr_qtd_profissional_solicitacao OWNER TO postgres;

--
-- TOC entry 1591 (class 1259 OID 18019)
-- Dependencies: 1693 3 317
-- Name: vwr_soma_cliente_solicitacao; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW vwr_soma_cliente_solicitacao AS
    SELECT vw_solicitacoes.cliente, sum(vw_solicitacoes.quantidade) AS quantidade FROM vw_solicitacoes GROUP BY vw_solicitacoes.cliente ORDER BY sum(vw_solicitacoes.quantidade) DESC, vw_solicitacoes.cliente;


ALTER TABLE public.vwr_soma_cliente_solicitacao OWNER TO postgres;

--
-- TOC entry 1885 (class 2604 OID 17867)
-- Dependencies: 1566 1567 1567
-- Name: codarea; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE area ALTER COLUMN codarea SET DEFAULT nextval('area_codarea_seq'::regclass);


--
-- TOC entry 1888 (class 2604 OID 17907)
-- Dependencies: 1574 1573 1574
-- Name: codcliente; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE cliente ALTER COLUMN codcliente SET DEFAULT nextval('cliente_codcliente_seq'::regclass);


--
-- TOC entry 1886 (class 2604 OID 17878)
-- Dependencies: 1568 1569 1569
-- Name: codprofissao; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE profissao ALTER COLUMN codprofissao SET DEFAULT nextval('profissao_codprofissao_seq'::regclass);


--
-- TOC entry 1887 (class 2604 OID 17889)
-- Dependencies: 1570 1571 1571
-- Name: codprofissional; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE profissional ALTER COLUMN codprofissional SET DEFAULT nextval('profissional_codprofissional_seq'::regclass);


--
-- TOC entry 1889 (class 2604 OID 17920)
-- Dependencies: 1576 1575 1576
-- Name: codsolicitacao; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE solicitacao ALTER COLUMN codsolicitacao SET DEFAULT nextval('solicitacao_codsolicitacao_seq'::regclass);


--
-- TOC entry 1916 (class 0 OID 17864)
-- Dependencies: 1567
-- Data for Name: area; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO area (codarea, descricao) VALUES (1, 'Informática');
INSERT INTO area (codarea, descricao) VALUES (2, 'Biológica');
INSERT INTO area (codarea, descricao) VALUES (3, 'Pornográfica');
INSERT INTO area (codarea, descricao) VALUES (4, 'teste');
INSERT INTO area (codarea, descricao) VALUES (6, 'Tráficoo');
INSERT INTO area (codarea, descricao) VALUES (7, 'Gambiarras');


--
-- TOC entry 1920 (class 0 OID 17904)
-- Dependencies: 1574
-- Data for Name: cliente; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO cliente (codcliente, nomecli, fantasia, endereco, bairro, cidade, uf, fonefixo, fonefax, fonecelular, email, homepage, tipo, datanasctoabertura, cpfcnpj, rgie) VALUES (1, 'Eidy', 'nenhuma', 'desconhecido', 'nao se sabe', 'nao se faz ideia', 'PR', '(99) 9999-9999', '(99) 9999-9999', '(99) 9999-9999', 'eidy@microsoft.com', 'www.google.com.br', '?', '2011-05-01', '012345678901234567', '012345678901234567');
INSERT INTO cliente (codcliente, nomecli, fantasia, endereco, bairro, cidade, uf, fonefixo, fonefax, fonecelular, email, homepage, tipo, datanasctoabertura, cpfcnpj, rgie) VALUES (2, 'Shishido', 'nenhuma', 'desconhecido', 'nao se sabe', 'nao se faz ideia', 'PR', '(22) 2222-2222', '(22) 2222-2222', '(22) 2222-2222', 'eidy@microsoft.com', 'www.google.com.br', '?', '2011-05-01', '222222222222222222', '222222222222222222');


--
-- TOC entry 1922 (class 0 OID 17923)
-- Dependencies: 1577
-- Data for Name: clientesprofissional; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO clientesprofissional (solicitacaocodsolicitacao, profissionalcodprofissional, aceito, nivel) VALUES (4, 2, true, 0);
INSERT INTO clientesprofissional (solicitacaocodsolicitacao, profissionalcodprofissional, aceito, nivel) VALUES (5, 3, true, 0);
INSERT INTO clientesprofissional (solicitacaocodsolicitacao, profissionalcodprofissional, aceito, nivel) VALUES (6, 4, true, 0);
INSERT INTO clientesprofissional (solicitacaocodsolicitacao, profissionalcodprofissional, aceito, nivel) VALUES (6, 1, true, 0);


--
-- TOC entry 1919 (class 0 OID 17897)
-- Dependencies: 1572
-- Data for Name: especializacao; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (1, 1, '2009-01-01', 5);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (1, 2, '2009-01-01', 5);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (1, 8, '2009-01-01', 5);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (2, 4, '2009-01-01', 3);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (2, 6, '2009-01-01', 7);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (2, 1, '2009-01-01', 7);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (3, 5, '2009-01-01', 2);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (3, 3, '2009-01-01', 8);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (4, 7, '2009-01-01', 9);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (4, 8, '2009-01-01', 1);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (4, 4, '2009-01-01', 1);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (1, 9, '2011-05-01', 1);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (2, 2, '2011-05-01', 10);
INSERT INTO especializacao (profissionalcodprofissional, profissaocodprofissao, dataespecializacao, nivel) VALUES (1, 6, '2012-12-21', 3);


--
-- TOC entry 1917 (class 0 OID 17875)
-- Dependencies: 1569
-- Data for Name: profissao; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (1, 'Analista de Sistemas', 1);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (2, 'Programador', 1);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (3, 'Cara das rede', 1);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (4, 'Médico', 2);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (5, 'Dentista', 2);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (6, 'Cirurgião', 2);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (7, 'Cafetão', 3);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (8, 'Ator', 3);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (9, 'testador', 4);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (10, 'Analista que nao Analisa', 1);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (11, 'testando', 1);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (14, 'acènto á', 1);
INSERT INTO profissao (codprofissao, descricao, areacodarea) VALUES (15, 'teste2', 4);


--
-- TOC entry 1918 (class 0 OID 17886)
-- Dependencies: 1571
-- Data for Name: profissional; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (1, '111.111.111-11', 'Diogo Hideki Matsumoto', 'nenhuma', 'm', '(99) 9999-9999', '(99) 9999-9999', 'Rua Qualquer', 'Centro', 'Ibipa', 'PR', '1.111.111-11', 'naosei', '2011-05-01', '01234567890123', '012345678901', '0123456789', '2011-05-01', '1991-07-26', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (2, '222.222.222-22', 'Aline Yukita', 'nenhuma', 'f', '(22) 2222-2222', '(22) 2222-2222', 'Rua Qualquer', 'Centro', 'Assaí', 'PR', '2.222.222-22', 'naosei', '2011-05-01', '22222222222222', '222222222222', '2222222222', '2011-05-01', '1991-02-06', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (3, '333.333.333-33', 'Marcio Edsao', 'nenhuma', 'm', '(33) 3333-3333', '(33) 3333-3333', 'Rua Qualquer', 'Centro', 'Maracai', 'PR', '3.333.333-33', 'naosei', '2011-05-01', '33333333333333', '333333333333', '3333333333', '2011-05-01', '1955-01-01', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (4, '444.444.444-44', 'Pablo Paulo da Silva', 'nenhuma', 'm', '(44) 4444-4444', '(44) 4444-4444', 'Rua Qualquer', 'Centro', 'Cornélio', 'PR', '4.444.444-44', 'naosei', '2011-05-01', '44444444444444', '444444444444', '4444444444', '2011-05-01', '1989-03-09', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (5, '111.111.111-19', 'Diogo Hideki Matsumoto 2', 'nenhuma', 'm', '(99) 9999-9999', '(99) 9999-9999', 'Rua Qualquer', 'Centro', 'Ibipa', 'PR', '1.111.111-11', 'naosei', '2011-05-18', '01234567890123', '012345678901', '0123456789', '2011-05-18', '2011-05-18', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (6, '111.111.111-20', 'Diogo Hideki Matsumoto 2', 'nenhuma', 'm', '(99) 9999-9999', '(99) 9999-9999', 'Rua Qualquer', 'Centro', 'Ibipa', 'PR', '1.111.111-11', 'naosei', '2011-05-20', '01234567890123', '012345678901', '0123456789', '2011-05-20', '2011-05-20', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (7, '111.111.111-21', 'Diogo Hideki Matsumoto 2', 'nenhuma', 'm', '(99) 9999-9999', '(99) 9999-9999', 'Rua Qualquer', 'Centro', 'Ibipa', 'PR', '1.111.111-11', 'naosei', '2011-05-20', '01234567890123', '012345678901', '0123456789', '2011-05-20', '2011-05-20', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (8, 'cepeeffe111111', 'nome', 'apelido', 'm', 'telefone telf', 'celeelecular', 'rua rua rua', 'bairro', 'cidade', 'PR', 'errige222222', 'orgaoe', '1999-10-10', 'cartera prof', 'ce ene aga', 'cnh cat', '1999-10-10', '1999-10-10', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (9, '              ', '', '', 'm', '', '', '', '', '', 'PR', '            ', '      ', '2012-12-21', '', '', '', '2012-12-21', '2012-12-21', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (10, '9             ', '', '', 'm', '', '', '', '', '', 'PR', '            ', '      ', '2012-12-21', '', '', '', '2012-12-21', '2012-12-21', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (11, '2             ', 'acènto', '', 'm', '', '', '', '', '', 'PR', '            ', '      ', '2012-12-21', '', '', '', '2012-12-21', '2012-12-21', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (12, '7             ', 'ac�nto �', '', 'm', '', '', '', '', '', 'PR', '8           ', '      ', '2012-12-21', '', '', '', '2012-12-21', '2012-12-21', false);
INSERT INTO profissional (codprofissional, cpf, nome, fantasia, sexo, fonefixo, fonecel, endereco, bairro, cidade, uf, rg_num, rg_org_exp, rg_data_exp, carteiraprofissional, cnh_registro, cnh_cat, cnh_vencto, datanascimento, alocado) VALUES (14, '09            ', 'ac�nto �', '', 'm', '', '', '', '', '', 'PR', '07          ', '      ', '2012-12-21', '', '', '', '2012-12-21', '2012-12-21', false);


--
-- TOC entry 1921 (class 0 OID 17917)
-- Dependencies: 1576
-- Data for Name: solicitacao; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (1, 1, 1, 2, '2011-04-16', '2011-04-19');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (2, 1, 2, 4, '2011-04-26', '2011-04-27');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (3, 1, 3, 2, '2011-04-26', '2011-04-27');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (4, 2, 4, 1, '2011-04-23', '2011-04-29');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (5, 2, 5, 1, '2011-04-23', '2011-04-30');
INSERT INTO solicitacao (codsolicitacao, clientecodcliente, profissaocodprofissao, qtde, datasolicitacao, dataatendimento) VALUES (6, 2, 8, 2, '2011-04-29', '2011-05-01');


--
-- TOC entry 1892 (class 2606 OID 17872)
-- Dependencies: 1567 1567
-- Name: area_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY area
    ADD CONSTRAINT area_pkey PRIMARY KEY (codarea);


--
-- TOC entry 1902 (class 2606 OID 17914)
-- Dependencies: 1574 1574
-- Name: cliente_cpfcnpj_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY cliente
    ADD CONSTRAINT cliente_cpfcnpj_key UNIQUE (cpfcnpj);


--
-- TOC entry 1904 (class 2606 OID 17912)
-- Dependencies: 1574 1574
-- Name: cliente_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY cliente
    ADD CONSTRAINT cliente_pkey PRIMARY KEY (codcliente);


--
-- TOC entry 1908 (class 2606 OID 17928)
-- Dependencies: 1577 1577 1577
-- Name: clientesprofissional_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY clientesprofissional
    ADD CONSTRAINT clientesprofissional_pkey PRIMARY KEY (solicitacaocodsolicitacao, profissionalcodprofissional);


--
-- TOC entry 1900 (class 2606 OID 17901)
-- Dependencies: 1572 1572 1572
-- Name: especializacao_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY especializacao
    ADD CONSTRAINT especializacao_pkey PRIMARY KEY (profissionalcodprofissional, profissaocodprofissao);


--
-- TOC entry 1894 (class 2606 OID 17883)
-- Dependencies: 1569 1569
-- Name: profissao_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY profissao
    ADD CONSTRAINT profissao_pkey PRIMARY KEY (codprofissao);


--
-- TOC entry 1896 (class 2606 OID 17896)
-- Dependencies: 1571 1571
-- Name: profissional_cpf_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY profissional
    ADD CONSTRAINT profissional_cpf_key UNIQUE (cpf);


--
-- TOC entry 1898 (class 2606 OID 17894)
-- Dependencies: 1571 1571
-- Name: profissional_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY profissional
    ADD CONSTRAINT profissional_pkey PRIMARY KEY (codprofissional);


--
-- TOC entry 1906 (class 2606 OID 17922)
-- Dependencies: 1576 1576
-- Name: solicitacao_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY solicitacao
    ADD CONSTRAINT solicitacao_pkey PRIMARY KEY (codsolicitacao);


--
-- TOC entry 1914 (class 2606 OID 17954)
-- Dependencies: 1905 1577 1576
-- Name: fkclientespr183846; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY clientesprofissional
    ADD CONSTRAINT fkclientespr183846 FOREIGN KEY (solicitacaocodsolicitacao) REFERENCES solicitacao(codsolicitacao);


--
-- TOC entry 1915 (class 2606 OID 17959)
-- Dependencies: 1897 1577 1571
-- Name: fkclientespr72559; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY clientesprofissional
    ADD CONSTRAINT fkclientespr72559 FOREIGN KEY (profissionalcodprofissional) REFERENCES profissional(codprofissional);


--
-- TOC entry 1910 (class 2606 OID 17934)
-- Dependencies: 1897 1571 1572
-- Name: fkespecializ744318; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY especializacao
    ADD CONSTRAINT fkespecializ744318 FOREIGN KEY (profissionalcodprofissional) REFERENCES profissional(codprofissional);


--
-- TOC entry 1911 (class 2606 OID 17939)
-- Dependencies: 1572 1569 1893
-- Name: fkespecializ926790; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY especializacao
    ADD CONSTRAINT fkespecializ926790 FOREIGN KEY (profissaocodprofissao) REFERENCES profissao(codprofissao);


--
-- TOC entry 1909 (class 2606 OID 17929)
-- Dependencies: 1569 1891 1567
-- Name: fkprofissao66504; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY profissao
    ADD CONSTRAINT fkprofissao66504 FOREIGN KEY (areacodarea) REFERENCES area(codarea);


--
-- TOC entry 1913 (class 2606 OID 17949)
-- Dependencies: 1569 1576 1893
-- Name: fksolicitaca640088; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY solicitacao
    ADD CONSTRAINT fksolicitaca640088 FOREIGN KEY (profissaocodprofissao) REFERENCES profissao(codprofissao);


--
-- TOC entry 1912 (class 2606 OID 17944)
-- Dependencies: 1574 1903 1576
-- Name: fksolicitaca741811; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY solicitacao
    ADD CONSTRAINT fksolicitaca741811 FOREIGN KEY (clientecodcliente) REFERENCES cliente(codcliente);


--
-- TOC entry 1927 (class 0 OID 0)
-- Dependencies: 3
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2011-06-08 20:38:08 BRT

--
-- PostgreSQL database dump complete
--

